from itertools import groupby
from operator import itemgetter

from db.db_util import dataframe_from_db
from util.common import *

if __name__ == '__main__':

    file_path = '/Users/lujiewen/asiainfo-sec/fujian-data/source/GRADE_ASSET.xlsx'
    df = pd.read_excel(file_path, usecols=[0, 1, 2, 10],
                       names=['id', 'name', 'system', 'ip'])
    records = df.to_dict(orient='records')

    system_assect = [list(group) for _, group in groupby(records, key=itemgetter('system'))]

    compitems = dataframe_from_db("""
        SELECT
            * 
        FROM
            tf_sa_compliance_item
        WHERE
            id IN ( "SEC-CI-LP-275", "SEC-CI-LP-292", "SEC-CI-LP-320", "SEC-CI-LP-322", "SEC-CI-LP-335", 
            "SEC-CI-LP-403", "SEC-CI-LP-442", "SEC-CI-LP-478", "SEC-CI-LP-479", "SEC-CI-LP-499", "SEC-CI-LP-513",
             "SEC-CI-LP-52" );
    """)

    w_sql_list = []

    for createTime in generate_workdays('2024-01-02', '2024-11-28', 1):
        assets = random.choice(system_assect)

        start_date = datetime.strptime(createTime, '%Y-%m-%d %H:%M:%S')

        for asset in assets:
            for row in compitems.itertuples():
                seep = {
                    "id": "GRREZX" + random_string(14),
                    "ip": asset['ip'],
                    "name": asset['name'],
                    "compNo": row.ID,
                    "compName": row.name,
                    "startTime": start_date,
                    "ipid": None,
                    "opin": None,
                    "assertId": asset['id'],
                    "content": row.content,
                    "stand": row.stand,
                    "step": row.item_step,
                    "ipval": None,
                }
                message1 = render_template("""
                        INSERT INTO tf_sa_gradepro_result (id, asset_ip, asset_name, comp_no, comp_name, source, tips,
                                   review_result, review_date, reviewer, sender, is_sended,
                                   send_date, change_id, close_date, dept, fac, facres, bossres,
                                   advice, fac_opin, reinforce_status, reinforce_desc, finder,
                                   report_status, report_date, find_date, reinforce_plandate,
                                   reinforcer, reinforce_date, opt_id, times, business_id,
                                   item_opin, asset_type, reinforce_scheme, asset_id, collect_echo,
                                   review_attach, review_proposal, remark, summary, retreat_advice,
                                   facdeal, comp_content, comp_stand, check_step, suit_range, cps,
                                   val_exp, check_type, IP_VAL)
                        VALUES ($id, $ip, $name, $compNo,
                                $compName, '1', NULL,
                                '-1', NULL, '', '0', '2', NULL, NULL, NULL, NULL, NULL, NULL, '0', NULL, NULL, '-1',
                                NULL, '0', NULL, NULL, $startTime, NULL, NULL, NULL,
                                $ipid, 1, '0',
                                $opin,
                                '110', NULL, $assertId, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
                                $content,
                                $stand,
                                $step,
                                'Linux', 'LINUX-129,LINUX-130,LINUX-131,LINUX-132,LINUX-133', '$$1 & $$2 & $$3 & $$4 & $$5', '1',
                                $ipval);
                              """, seep)
                w_sql_list.append(message1)

    write_file('target/SATP_等保自评.sql', w_sql_list)
